#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
reload(sys)
sys.setdefaultencoding('utf-8')

import dbs
import confs

class CMDB(object):
    def __init__(self):
        self.conn, self.cur = dbs.db_connect()


    def list_host(self):
        query = '''
            SELECT 
                cmdb.id,
                assets_number,
                IDC_type,
                room_info,
                cabinet_info,
                serial_number,
                model_number,
                wan_ip,
                ip,
                remote_ip,
                status,
                assets_user,
                business_type,
                business_name,
                detailed_action,
                upline_time,
                downline_time,
                osystem,
                source,
                deadline,
                contract_number,
                price,
                monthly_fee,
                up_time,
                down_time,
                usage_days,
                monthbilling,
                comment,
                cpu,
                mem,
                disk
            FROM
                cmdb'''

        self.cur.execute(query)
        data = self.cur.fetchall()
        return {'code': 0, 'message': '获取机器列表成功', 'data': data}

    # 根据ID 查询单个host 信息
    def hostInfobyID(self, hostID):
        query = '''SELECT
                id,
                assets_number,
                IDC_type,
                room_info,
                cabinet_info,
                serial_number,
                model_number,
                wan_ip,
                ip,
                remote_ip,
                status,
                assets_user,
                business_name,
                business_type,
                detailed_action,
                upline_time,
                downline_time,
                osystem,
                source,
                deadline,
                contract_number,
                price,
                monthly_fee,
                up_time,
                down_time,
                usage_days,
                monthbilling,
                comment,
                appliction,
                port,
                username,
                password,
                cpu,
                mem,
                disk,
                bandwidth,
                room_id,
                cabinet_id,
                location
            FROM
                cmdb
            WHERE
            id = {hostID}'''.format(hostID=hostID)
        self.cur.execute(query)
        data = self.cur.fetchone()
        return {'code': 0, 'message': '获取机器列表成功', 'data': data}


    def exsit_host(self, assets_number):
        query = 'SELECT assets_number FROM cmdb WHERE assets_number="{assets_number}"'.format(assets_number=assets_number)
        self.conn, self.cur = dbs.db_connect()
        if self.cur.execute(query):
            return True
        else:
            return False

    # 添加主机
    def add_host(self, assets_number, wan_ip, serial_number, username, password, port):
        if self.exsit_host(assets_number):
            return {'code': 59, 'message': confs._code.get(59, '主机已经存在')}

        query = '''
            INSERT INTO cmdb(assets_number, wan_ip, serial_number, username, password, port) VALUES (
            '{assets_number}','{wan_ip}','{serial_number}','{username}','{password}','{port}'
            )'''.format(
            assets_number=assets_number,
            wan_ip=wan_ip,
            serial_number=serial_number,
            username=username,
            password=password,
            port=port
        )

        try:
            self.conn, self.cur = dbs.db_connect()
            self.cur.execute(query)
            self.conn.commit()
            return {'code': 0, 'message': confs._code.get(0, '成功')}
        except Exception as e:
            return {'code': 803, 'message': e}

    # 修改主机
    def modify_host(self, id, assets_number, IDC_type, room_info, cabinet_info, serial_number, model_number, wan_ip,
                    ip, remote_ip, status, assets_user, business_type, business_name, detailed_action, upline_time,
                    downline_time, osystem, source, deadline, contract_number, price, monthly_fee, up_time, down_time,
                    usage_days, monthbilling, comment, cpu, mem, disk):
        query = ''' UPDATE cmdb SET assets_number="{assets_number}", IDC_type="{IDC_type}", room_info="{room_info}",
                    cabinet_info="{cabinet_info}", serial_number="{serial_number}", model_number="{model_number}",
                    wan_ip="{wan_ip}", ip="{ip}", remote_ip="{remote_ip}", cmdb.status="{status}", assets_user="{assets_user}",
                    business_type="{business_type}", business_name="{business_name}", detailed_action="{detailed_action}",
                    upline_time="{upline_time}", downline_time="{downline_time}", osystem="{osystem}", source="{source}",
                    deadline="{deadline}", contract_number="{contract_number}", price="{price}", monthly_fee="{monthly_fee}",
                    up_time="{up_time}", down_time="{down_time}", usage_days="{usage_days}", monthbilling="{monthbilling}",
                    cmdb.comment="{comment}", cpu="{cpu}", mem="{mem}", cmdb.disk="{disk}"
                    WHERE id="{id}"
                    '''.format(id=id, assets_number=assets_number, IDC_type=IDC_type, room_info=room_info,
                                cabinet_info=cabinet_info, serial_number=serial_number, model_number=model_number,
                                wan_ip=wan_ip, ip=ip, remote_ip=remote_ip, status=status, assets_user=assets_user,
                                business_type=business_type, business_name=business_name, detailed_action=detailed_action,
                                upline_time=upline_time, downline_time=downline_time, osystem=osystem, source=source,
                                deadline=deadline, contract_number=contract_number, price=price, monthly_fee=monthly_fee,
                                up_time=up_time, down_time=down_time, usage_days=usage_days, monthbilling=monthbilling,
                                comment=comment, cpu=cpu, mem=mem, disk=disk)

        if downline_time == "None":
            query = query.replace('downline_time="None",', '')
        if deadline == "None":
            query = query.replace('deadline="None",', '')
        if up_time == "None":
            query = query.replace('up_time="None",', '')
        if down_time == "None":
            query = query.replace('down_time="None",', '')
        self.cur.execute(query)
        self.conn.commit()
        return {'code': 60, 'message': '修改机器成功'}


    # 删除主机
    def del_host(self, id):
        query = 'DELETE FROM cmdb WHERE id="{id}"'.format(id=id)
        self.cur.execute(query)
        self.conn.commit()
        return {'code': 0, 'message': '删除机器成功'}

# 机房方法
    # 添加机房
    def add_room(self, info):
        query = 'INSERT INTO room(info) VALUES("{info}")'.format(info=info)
        try:
            self.cur.execute(query)
            self.conn.commit()
            return {'code': 0, 'message': '添加机房  {0}  成功'.format(info)}
        except Exception, e:
            return {'code': 202, 'message': '数据库执行失败' + str(e)}

    # 修改机房
    def edit_room(self, id, info):
        query = 'UPDATE room set info="{info}" WHERE id="{id}"'.format(id=id,
                                                                       info=info)
        try:
            self.cur.execute(query)
            self.conn.commit()
            return {'code': 0, 'message': '修改机房  {0}  成功'.format(info)}
        except Exception, e:
            return {'code': 202, 'message': '数据库执行失败' + str(e)}

    # 删除机房
    def del_room(self, id):
        query = 'DELETE fROM room WHERE id="{id}"'.format(id=id)
        try:
            self.cur.execute(query)
            self.conn.commit()
            return {'code': 0, 'message': '删除机房成功'}
        except Exception, e:
            return {'code': 202, 'message': '数据库执行失败' + str(e)}

    # 机房列表
    def list_room(self):
        query = 'SELECT id,info FROM room'
        try:
            self.cur.execute(query)
            data = self.cur.fetchall()
            return {'code': 0, 'message': '获取机房列表成功', 'data': data}
        except Exception, e:
            return {'code': 202, 'message': '数据库执行失败' + str(e)}

    # 机柜方法
    # 添加机柜
    def add_cabinet(self, info):
        query = 'INSERT INTO cabinet(info) VALUES("{info}")'.format(info=info)
        self.cur.execute(query)
        self.conn.commit()
        return {'code': 0, 'message': '添加机柜  {0}  成功'.format(info)}

    # 删除机柜
    def del_cabinet(self, id):
        query = 'DELETE fROM cabinet WHERE id="{id}"'.format(id=id)
        self.cur.execute(query)
        self.conn.commit()
        return {'code': 0, 'message': '删除机柜成功'}

    # 机柜列表
    def list_cabinet(self):
        query = 'SELECT id,info FROM cabinet'
        self.cur.execute(query)
        data = self.cur.fetchall()
        return {'code': 0, 'message': '获取机柜列表成功', 'data': data}

    # 修改机柜
    def edit_cabinet(self, id, info):
        query = 'UPDATE cabinet set info="{info}" WHERE id="{id}"'.format(id=id,
                                                                          info=info)
        self.cur.execute(query)
        self.conn.commit()
        return {'code': 0, 'message': '修改机柜成功'}



if __name__ == '__main__':
    c = CMDB()
    #c.load_excel()
    #c.exsit_host(assets_number='efun-cloudAWS-00879')
    #c.add_host(assets_number='efun-cloudAWS-00879', wan_ip='52.28.68.123', password='123456', port=22, serial_number='i-0699643449b879d08', username='root')







